use crate::error::new_err_info;
use crate::error::AppErr;
use crate::error::SqlxErr;
use crate::error::EC_NO_IMAGE;
use crate::error::EC_PRODUCT_IS_EXISTS;
use serde::Deserialize;
use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqliteConnection;

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_product (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    price INTEGER NOT NULL, 
    image BLOB NOT NULL,
    UNIQUE (name)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn create(name: &str, price: u32, image: &[u8]) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;

    let ret = match is_exists(&mut tx, name).await? {
        Some(_) => Err(new_err_info(EC_PRODUCT_IS_EXISTS, "商品名称重复")),
        None => Ok(create_impl(&mut tx, name, price, image).await?),
    };

    tx.commit().await?;

    ret
}

async fn is_exists(tx: &mut SqliteConnection, name: &str) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(r#"SELECT id FROM tb_product WHERE name = ?"#)
        .bind(name)
        .fetch_optional(tx)
        .await?;
    Ok(row.and_then(|r| r.get(0)))
}

async fn create_impl(
    tx: &mut SqliteConnection,
    name: &str,
    price: u32,
    image: &[u8],
) -> Result<i64, SqlxErr> {
    let row = sqlx::query(
        r#"
        INSERT INTO tb_product (name, price, image) VALUES (?, ?, ?)
    "#,
    )
    .bind(name)
    .bind(price)
    .bind(image)
    .execute(tx)
    .await?;

    Ok(row.last_insert_rowid())
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Product {
    pub id: i64,
    pub name: String,
    pub price: u32,
    #[serde(with = "serde_bytes")]
    pub image: Box<[u8]>,
}

#[derive(Debug, Serialize)]
pub struct ProductNoImage {
    pub id: i64,
    pub name: String,
    pub price: u32,
}   

pub async fn query_no_image() -> Result<Box<[ProductNoImage]>, SqlxErr> {
    let rows = sqlx::query(r#"
        SELECT id, name, price FROM tb_product
    "#)
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let product = ProductNoImage {
            id: row.get(0),
            name: row.get(1),
            price: row.get(2),
        };
        vec.push(product);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn get_image(id: i64) -> Result<Box<[u8]>, AppErr> {
    let row = sqlx::query(r#"SELECT image FROM tb_product WHERE id = ? LIMIT 1"#)
    .bind(id)
    .fetch_optional(get_pool())
    .await?
    .ok_or(new_err_info(EC_NO_IMAGE, "没有图片"))?;

    let body = row.get(0);

    Ok(body)
}

pub async fn query() -> Result<Box<[Product]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, price, image FROM tb_product
    "#,
    )
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let product = Product {
            id: row.get(0),
            name: row.get(1),
            price: row.get(2),
            image: row.get(3),
        };
        vec.push(product);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn edit(product: &Product) -> Result<(), SqlxErr> {
    sqlx::query(r#"
        UPDATE tb_product SET name = ?, price = ?, image = ? WHERE id = ?
    "#)
    .bind(&product.name)
    .bind(product.price)
    .bind(&product.image)
    .bind(&product.id)
    .execute(get_pool())
    .await?;
    Ok(())
}

pub async fn query_by_device_id(device_id: i64) -> Result<Box<[Product]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT tb_product.id, tb_product.name, tb_product.price, tb_product.image
        FROM tb_cargo_config
        JOIN tb_product ON tb_cargo_config.product_id = tb_product.id
        WHERE tb_cargo_config.device_id = ?
    "#,
    )
    .bind(device_id)
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let product = Product {
            id: row.get(0),
            name: row.get(1),
            price: row.get(2),
            image: row.get(3),
        };
        vec.push(product);
    }
    Ok(vec.into_boxed_slice())
}
